﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Business.Compta;
using System.Configuration;
using System.Data.OleDb;
using System.Windows.Media;
using DAO;

namespace DAO.Compta
{
    public  class ComptaCategoriesDAO
    {

        private static string ID = MainDAO.ID;
        private static string ID_ACCOUNT = MainDAO.ID_ACCOUNT;
        private static string ID_USER = MainDAO.ID_USER;
        private static string CATEGORIES_PER_USER = MainDAO.CATEGORIES_PER_USER;
        private static string PERCENT = MainDAO.PERCENT;
        private static string ID_CATEG = MainDAO.ID_CATEG;

        public static List<Category> getAllCategories()
        {
            List<Category> _return = new List<Category>();
            OleDbConnection conn = MainDAO.OpenComptaConnection();
            OleDbCommand myOleDbCommand2;
            myOleDbCommand2 = new OleDbCommand("SELECT * from CATEGORIES", conn);
            OleDbDataReader myOleDbDataReader = myOleDbCommand2.ExecuteReader();
            int id;
            string categoryName, categoryColor;

            while (myOleDbDataReader.Read())
            {
                id = myOleDbDataReader.GetInt32(0);
                categoryName = (string)myOleDbDataReader.GetValue(1);
                categoryColor = (string)myOleDbDataReader.GetValue(2);
                _return.Add(new Category(id, categoryName, (Color)ColorConverter.ConvertFromString(categoryColor)));

            }
            _return.Add(new Category(-1, null, Colors.White));
            myOleDbDataReader.Close();
            conn.Close();
            return _return;
        }


        public static void ModifyCategory(Category line)
        {
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            OleDbCommand myCommand = new OleDbCommand();
            myCommand.Connection = conn;
            myCommand.CommandText = "update CATEGORIES set CATEGORY_NAME = @categName, CATEGORY_COLOR = @categColor where ID = @id";
            myCommand.Parameters.Add("@categName", OleDbType.VarChar).Value = line.CategoryName;
            myCommand.Parameters.Add("@categColor", OleDbType.VarChar).Value = line.Color.ToString();
            myCommand.Parameters.Add("@id", OleDbType.Integer).Value = line.IdCategory;

            myCommand.ExecuteNonQuery();
            conn.Close();
        }

        public static int InserNewCategory(Category line)
        {
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            OleDbCommand myCommand = new OleDbCommand();
            myCommand.CommandText = "insert into CATEGORIES(CATEGORY_NAME, CATEGORY_COLOR) VALUES (@categName,@categColor)";
            myCommand.Parameters.Add("@categName", OleDbType.VarChar).Value = line.CategoryName;
            myCommand.Parameters.Add("@categColor", OleDbType.VarChar).Value = line.Color.ToString();

            OleDbTransaction trans = conn.BeginTransaction();
            myCommand.Transaction = trans;
            myCommand.Connection = conn;
            myCommand.ExecuteNonQuery();
            OleDbCommand myOleDbCommand2;
            myOleDbCommand2 = new OleDbCommand("SELECT @@IDENTITY", conn);
            myOleDbCommand2.Transaction = trans;
            OleDbDataReader myOleDbDataReader = myOleDbCommand2.ExecuteReader();
            myOleDbDataReader.Read();

            int executeInsertGetIdentity = (int)myOleDbDataReader.GetValue(0);
            trans.Commit();
            myOleDbDataReader.Close();
            conn.Close();
            return executeInsertGetIdentity;
        }

        public static int deleteCategory(Category line)
        {
            int _return = -1;
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            //// Read in the file
            OleDbCommand myCommand = new OleDbCommand();

            myCommand.CommandText = "delete from CATEGORIES where ID = @id";
            myCommand.Parameters.Add("@id", OleDbType.Integer).Value = line.IdCategory;
            myCommand.Connection = conn;
            _return = myCommand.ExecuteNonQuery();
            conn.Close();
            return _return;
        }


        public static List<CategoriesPerUser> getCategoriesPerUser(int idAccount)
        {
            List<CategoriesPerUser> _return = new List<CategoriesPerUser>();
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            OleDbCommand myOleDbCommand2;
            myOleDbCommand2 = new OleDbCommand("SELECT " + ID + "," + ID_USER + "," + ID_CATEG + "," + PERCENT +
                " from " + CATEGORIES_PER_USER +
                " where " + ID_ACCOUNT + " = @idAccount", conn);
            myOleDbCommand2.Parameters.Add("@idAccount", OleDbType.Integer).Value = idAccount;
            OleDbDataReader myOleDbDataReader = myOleDbCommand2.ExecuteReader();
            int id, idUser, idCateg;
            Double percent;

            while (myOleDbDataReader.Read())
            {
                id = myOleDbDataReader.GetInt32(0);
                idUser = (int)myOleDbDataReader.GetValue(1);
                idCateg = (int)myOleDbDataReader.GetValue(2);
                percent = (Double)myOleDbDataReader.GetValue(3);
                _return.Add(new CategoriesPerUser(id, idUser, idCateg, percent));
            }
            myOleDbDataReader.Close();
            conn.Close();
            return _return;
        }



        public static void ModifyCategoryPerUser(CategoriesPerUser categoryPerUser)
        {
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            OleDbCommand myCommand = new OleDbCommand();
            myCommand.Connection = conn;
            myCommand.CommandText = "update " + CATEGORIES_PER_USER + " set " + ID_USER + " = @idUser, " + ID_CATEG + " = @idCateg, " + PERCENT + " = @percent where " + ID + " = @id";
            myCommand.Parameters.Add("@idUser", OleDbType.Integer).Value = categoryPerUser.IdUser;
            myCommand.Parameters.Add("@idCateg", OleDbType.Integer).Value = categoryPerUser.IdCategory;
            myCommand.Parameters.Add("@percent", OleDbType.Double).Value = categoryPerUser.Percent;
            myCommand.Parameters.Add("@id", OleDbType.Integer).Value = categoryPerUser.Id;

            myCommand.ExecuteNonQuery();
            conn.Close();
        }

        public static int InserNewCategoryPerUser(CategoriesPerUser line)
        {
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            OleDbCommand myCommand = new OleDbCommand();
            myCommand.CommandText = "insert into " + CATEGORIES_PER_USER + " (" + ID_ACCOUNT + "," + ID_CATEG + "," + ID_USER + "," + PERCENT + ") VALUES (@idAccount,@idCateg,@idUser, @percent)";
            myCommand.Parameters.Add("@idAccount", OleDbType.Integer).Value = line.IdAccount;
            myCommand.Parameters.Add("@idCateg", OleDbType.Integer).Value = line.IdCategory;
            myCommand.Parameters.Add("@idUser", OleDbType.Integer).Value = line.IdUser;
            myCommand.Parameters.Add("@percent", OleDbType.Double).Value = line.Percent;

            OleDbTransaction trans = conn.BeginTransaction();
            myCommand.Transaction = trans;
            myCommand.Connection = conn;
            myCommand.ExecuteNonQuery();
            OleDbCommand myOleDbCommand2;
            myOleDbCommand2 = new OleDbCommand("SELECT @@IDENTITY", conn);
            myOleDbCommand2.Transaction = trans;
            OleDbDataReader myOleDbDataReader = myOleDbCommand2.ExecuteReader();
            myOleDbDataReader.Read();

            int executeInsertGetIdentity = (int)myOleDbDataReader.GetValue(0);
            trans.Commit();
            myOleDbDataReader.Close();
            conn.Close();
            return executeInsertGetIdentity;
        }

        public static int deleteCategoryPerUser(CategoriesPerUser line)
        {
            int _return;
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            //// Read in the file
            OleDbCommand myCommand = new OleDbCommand();

            myCommand.CommandText = "delete from " + CATEGORIES_PER_USER + " where ID = @id";
            myCommand.Parameters.Add("@id", OleDbType.Integer).Value = line.Id;
            myCommand.Connection = conn;
            _return = myCommand.ExecuteNonQuery();
            conn.Close();
            return _return;
        }

    }
}
